<?php
/////////////////////////////////////////////////////////////////////////////
// 
//
// workflow_system
//
// workflow_copyright
// 
/////////////////////////////////////////////////////////////////////////////

/**
 * FILE_NAME : database.core.php   FILE_PATH : \\classes\core\database.core.php
 * ....数据库操作类
 *
 * @copyright workflow_system 
 * @author workflow Develop Team 
 * @package 
 * @subpackage 
 * @version Thu Nov 08 13:36:35 CST 2007
 */

class DataBase extends DBEngine{
	/**
	 * 初始化数据库操作类
	 *
	 * @return DataBase
	 */
	function DataBase(){
		/**
		 * 初始化数据库连接
		 */
		$this->_dbinitialize();
		//Base_Use_Sign
		defined('Base_Use_Sign') or die("this is invalid system!please contact <a href='http://www.shopnc.net'>www.shopnc.net</>.");
	}
	/**
	 * 获得一条记录
	 *
	 * @param int $id 记录ID
	 * @param string $table_name 查询的表名
	 * @param string $field  记录ID字段名
	 * @param string $fields 查询的字段
	 * @return array $rs_row 返回数组形式的查询结果
	 */
	function GetOneRow($id,$table_name,$field,$fields="*"){
		$rs_sql = "select {$fields} from `".NC_DBTABLEPRE."$table_name` as {$table_name} where {$field}='" . $id . "'";
		$rs_row = $this->GetRow($rs_sql);
		return $rs_row;
	}

	/**
	 * 获得全部记录
	 *
	 * @param int $id 记录ID
	 * @param string $table_name 查询的表名
	 * @param string $fields 查询的字段
	 * @param string $order 查询的字段的排序方式
	 * @return array $rs_row 返回数组形式的查询结果
	 */
	function GetAllRow($table_name,$fields="*",$condition){
		$rs_sql = "select {$fields} from `".NC_DBTABLEPRE."$table_name` as {$table_name}  {$condition}";
		$rs_row = $this->GetArray($rs_sql);
		return $rs_row;
	}

	/**
	 * 得到某条特定记录
	 *
	 * @param var $condition 可以是条件语句，也可以是数组，如果是数组，则统一为相等计算，并且为and关系
	 * @param string $table_name 不带前缀的表的名称
	 * @param string $fields 所需要得到的列
	 * @param string $order 排序信息
	 * @param string $group 分组信息
	 * @return var[] 一维数组，形式如$arr[列名]
	 */
	function GetTheRow($condition,$table_name,$fields="*",$order = "",$group = ""){
		$condition_str = "";
		if (is_array($condition) == true){
			//如果是数组，那么数组的键为字段名称，组合成为查询条件
			foreach($condition as $k=>$v){
				$condition_str .= " and {$k} = {$v}";
			}
		}else{
			$condition_str = $condition;
		}
		//得到完整的SQL语句
		//$table_name,$condition = "",$fields = "*",$order = "",$group = ""
		$rs_sql = $this->GetSql($table_name,$condition_str,$fields,$order,$group);
		$rs_row = $this->GetRow($rs_sql);
		return $rs_row;
	}

	/**
	 * 组合多个表联结SQL语句
	 *
	 * @param string[] $table_array  放置表的数组
	 * @param string[] $join_str  放置表间连接类型，一般有inner join/left join
	 * @param string[] $on_array  放置联接条件的数组，其数组数量与联接类型数量一致
	 * @param string[] $fields_array  放置所需要的字段，其与表的数组位置要对应，无须再写上表名称，只许写上字段名称即可，例如$table_array = array('a','b','c'); $fields_array = array('字段1','','字段1');那所需要的字段为a.字段1,c.字段1
	 * @param string $where_str  条件语句，形式如， and 表名.字段 = 值
	 * @param string[] $order_array  其用法与$fields_array类似
	 * @param string[] $group_array  其用法与$fields_array类似
	 * @return string
	 */
	function GetJoinTableSql($table_array,$join_str,$on_array,$fields_array = array('*'),$where_str = "",$order_array = array(),$group_array = array()){
		$from_str = "";
		$select_str = "";
		$order_str = "";
		$group_str = "";

		if(is_array($table_array) && is_array($on_array) && is_array($fields_array)){

			//必须是2个或以上的表进行关联
			if(count($table_array) <= 1){
				return "";
			}
			//表的数量比联接类型大1
			if(count($table_array) != count($on_array) + 1){
				return "";
			}
			if(count($table_array) < count($fields_array)){
				return "";
			}

			//组合from部分SQL语句
			foreach($table_array as $k=>$v){

				if (!strstr($v," as ")){
					$rename = " as " . $v;
				}else{
					$allname = $v;

					$v = trim(substr($v,0,strpos($v,"as")));
					$rename = strstr($allname," as ");
					$table_array[$k] = str_replace(" as ","",$rename);
				}
				
				if($k == 0){
					$from_str .= "`" . NC_DBTABLEPRE.$v . "` " . $rename;
				}else{
					$from_str = $from_str . " " . $join_str . " `" . NC_DBTABLEPRE.$v ."` " . $rename . " on " . $on_array[$k - 1] . " ";
				}
			}

			//组合SELECT部分的SQL语句
			foreach($fields_array as $k=>$v){
				if (trim($v) != ""){
					$f_arr = explode(",",$v);
					foreach ($f_arr as $f_k=>$f_v){
						if (strpos($f_v,"(") > 0 && strpos($f_v,"*") < 0){
							$f_arr[$f_k] = str_replace("(","(" . $table_array[$k] . "." ,$f_v);
						}else if(strpos($f_v,"(") > 0 && strpos($f_v,"*") > 0){
							$f_arr[$f_k] = $f_v;
						}else{
							$f_arr[$f_k] = $table_array[$k] . "." . $f_v;
						}
					}
					$fields_array[$k] = implode(",",$f_arr);
					$select_str .= $fields_array[$k] . ",";
				}
			}
			$select_str = trim($select_str);
			if (substr($select_str,strlen($select_str)-1,1) == ","){
				$select_str = substr($select_str,0,strlen($select_str)-1);
			}

			//WHERE部分SQL语句
			$where_str = $this->_conditionWhere($where_str);

			//组合Order部分的SQL语句
			foreach($order_array as $k=>$v){
				if (trim($v) != ""){
					$f_arr = explode(",",$v);
					foreach ($f_arr as $f_k=>$f_v){
						$f_arr[$f_k] = $table_array[$k] . "." . trim($f_v);
					}
					$order_array[$k] = implode(",",$f_arr);
					$order_str .= $order_array[$k] . ",";
				}
			}
			$order_str = trim($order_str);
			if (trim($order_str) != ""){
				if (substr($order_str,strlen($order_str)-1,1) == ","){
					$order_str = substr($order_str,0,strlen($order_str)-1);
				}
				$order_str = " order by " . $order_str;
			}
			//组合Group部分的SQL语句
			foreach($group_array as $k=>$v){
				if (trim($v) != ""){
					$f_arr = explode(",",$v);
					foreach ($f_arr as $f_k=>$f_v){
						$f_arr[$f_k] = $table_array[$k] . "." . trim($f_v);
					}
					$group_array[$k] = implode(",",$f_arr);
					$group_str .= $group_array[$k] . ",";
				}
			}
			$group_str = trim($group_str);
			if (trim($group_str) != ""){
				if (substr($group_str,strlen($group_str)-1,1) == ","){
					$group_str = substr($group_str,0,strlen($group_str)-1);
				}
				$group_str = " group by " . $group_str;
			}
			return "select " . $select_str . " from " . $from_str . " " . $where_str . " " . $order_str . " " . $group_str;
		}else{
			return false;
		}
	}

	/**
	 * 构造最终应用于SQL查询的where检索语句
	 *
	 * @param string $condition_str 构造后的SQL检索语句字符串
	 * @return string $condition_str 返回完整的where检索语句字符串
	 */
	function _conditionWhere($condition_str){
		if(substr(trim($condition_str),0,3)=="and"){
			$condition_str = " where " . substr(trim($condition_str),3);
		}
		return $condition_str;
	}

	/**
	 * 返回符合条件的记录总数
	 *
	 * @param string $table_name  表名
	 * @param string $condition  条件
	 * @return int
	 */
	function CountRows($table_name, $condition){
		$row_sql = "SELECT count(*) as count FROM `".NC_DBTABLEPRE."$table_name` as {$table_name} ";
		if("" != $condition){
			$row_sql .= " where " . $condition;
		}
		$count = $this->GetOne($row_sql);
		return $count;
	}

	/**
	 * 根据条件更新多条记录
	 *
	 * @param array $update_array 更新的字段及值,一维数组
	 * @param string $table_name 要更新的表的名称
	 * @param string $condition 更新条件
	 * @param string $genre 更新的类型,common普通直接付值,formula函数或运算形式的付值
	 * @return boolen
	 */
	function UpdateRows($table_name, $update_array, $condition = "",$genre = "common"){
		$row_sql = "UPDATE `".NC_DBTABLEPRE."$table_name` SET";
		if(is_array($update_array)){
			if (file_exists(BasePath."/share/filterstring.inc.php")){
				include_once("filter.class.php");
				include (BasePath."/share/filterstring.inc.php");
				$update_array = FilterClass::outFilterString($_filterstringCacheArray,$update_array);
			}
			foreach ($update_array as $key => $value){
				if($genre == "common"){
					$set_string .= ", {$key} = '" . $value . "'";
				}else if ($value == "null" || $genre == "formula"){
					$set_string .= ", {$key} = " . $value;
				}
			}
			$row_sql .= substr($set_string, 1);
		}else{
			return false;
		}

		if("" != $condition){
			$row_sql .= " where " . $this->_DropName($condition,$table_name);
		}

		return $update_rs = $this->Execute($row_sql);
	}

	/**
	 * 更新一条记录
	 *
	 * @param int $id 更新记录的ID值
	 * @param array $update_array 更新字段数组
	 * @param string $table_name 数据表名
	 * @param string $field 更新记录ID的字段名
	 * @param boolen $intfield 是否是整型类的不加引号参数，或字段计算等不能加参数的情况，使用true
	 * @return boolean
	 */
	function UpdateRow($id, $update_array, $table_name, $field, $intfield=false){
        //判断更新数组是否为空，如果为空，则返回false
        if (empty($update_array)){
        	return false;
        }
		if($this->_dbconfig['engine_type'] == "adodb"){
			$row_sql = "select * from `".NC_DBTABLEPRE."$table_name` as {$table_name} where {$field} = '" . $id ."'";
			$update_rs = $this->Execute($row_sql);
			$update_sql = $this->GetUpdateSQL($update_rs, $update_array);
		}else{
			if (file_exists(BasePath."/share/filterstring.inc.php") && $intfield == false){
				include_once("filter.class.php");
				include (BasePath."/share/filterstring.inc.php");
				$update_array = FilterClass::outFilterString($_filterstringCacheArray,$update_array);
			}
			$update_sql = $this->GetUpdateSQL(NC_DBTABLEPRE.$table_name, $update_array , "where {$field} = '" . $id ."'", $intfield);
		}
		if(!empty($update_sql)){
			if ($this->Execute($update_sql)){
				return true;
			}else{
				return false;
			}
		}else{
			return false;
		}

	}

	/**
	 * 插入一条记录
	 *
	 * @param array $insert_array 插入字段数组
	 * @param string $table_name 插入数据表名
	 * @param string $field 表ID字段 
	 * @return boolean
	 */
	function InsertRow($insert_array, $table_name, $field){
		if($this->_dbconfig['engine_type'] == "adodb"){
			$insert_sql = "select * from `".NC_DBTABLEPRE."$table_name` where {$field}='-1'";
			$insert_rs = $this->Execute($insert_sql);
			$insertSql = $this->GetInsertSQL($insert_rs, $insert_array);
		}else{
			if (file_exists(BasePath."/share/filterstring.inc.php")){
				include_once("filter.class.php");
				include (BasePath."/share/filterstring.inc.php");
				$insert_array = FilterClass::outFilterString($_filterstringCacheArray,$insert_array);
			}
			$insertSql = $this->GetInsertSQL(NC_DBTABLEPRE.$table_name, $insert_array);
		}
		return $this->Execute($insertSql);
	}

	/**
	 * 删除一条或多条记录
	 *
	 * @param array or int $del_array 删除记录ID或ID数组
	 * @param string $table_name 删除记录所在表名
	 * @param string $field 删除记录匹配字段
	 * @param string $conditon  删除条件
	 * @return bool
	 */
	function DelRow($del_array,$table_name,$field,$conditon = ""){
		if(is_array($del_array)){
			foreach ($del_array as $value){
				$this->DeleOneRow($value,$table_name,$field,$conditon);
			}
			return true;
		}else{
			return $this->DeleOneRow($del_array,$table_name,$field,$conditon);
		}
	}
	/**
	 * 删除某一条记录
	 *
	 * @param string $del_value  要删除的字段值
	 * @param string $table_name  删除记录所在表名
	 * @param string $field  删除记录匹配字段
	 * @param string $conditon  删除条件
	 * @return boolen
	 */
	function DeleOneRow($del_value = "",$table_name,$field = "",$conditon = ""){
		if (trim($del_value) != "" && trim($field) != ""){
			$conditon_str = " and {$field} = '" . $del_value . "'";
		}
		$conditon_str .= " " . $conditon;
		
		//禁止对数据库进行整体删除
		$str = $this->_DropName($this->_conditionWhere($conditon_str),$table_name);
		if (trim($str) != ''){
			$del_sql = "delete from `".NC_DBTABLEPRE."$table_name` " . $str;
			//监控删除语句
			$insert_array = array();
			$insert_array['date_line'] = time();
			$insert_array['delete_result'] = addslashes($del_sql);
			$this->InsertRow($insert_array, 'delete_debug', 'delete_debug_id');
			$del_rs = $this->Execute($del_sql);
			return $del_rs;
		}else {
			return false;
		}
	}

	/**
	 * 获得最后一条记录
	 *
	 * @param string $table_name
	 * @param string $field
	 * @param string $conditionstr
	 * @param string $order 排序语句
	 * @param string $group 分组语句
	 * @return array $rs_row
	 */
	function GetLastRow($table_name, $field, $conditionstr="",$order="",$group=""){
		//得到完整的SQL语句
		$rs_sql = $this->GetSql($table_name,$conditionstr,"Max({$field}) as {$field}",$order,$group);
		$rs_row = $this->GetRow($rs_sql);
		return $rs_row;
	}

	/**
     * 获得列表的操作
     *
     * @param string $obj_condition
     * @param object $obj_page
     * @param string $tablename
     * @param string $fields  取得特定字段，默认为*
     * @param string $order  排序语句，如member_id desc
     * @param string $group  分组语句
     * @param int $count 需要的记录数量
     * @return array
     */
	function GetList($conditionstr = "",&$obj_page,$table_name,$fields="*",$count = 0,$order = "",$group = ""){
		//得到完整的SQL语句
		$rs_sql = $this->GetSql($table_name,$conditionstr,$fields,$order,$group);
		return $this->GetResultArray($rs_sql,&$obj_page,$count);
	}

	/**
	 * 根据分页对象的设置，获取当前页面的数据列表
	 *
	 * @param object $obj_page  分页对象
	 * @param string[] $table_array  放置表的数组
	 * @param string[] $join_str  放置表间连接类型，一般有inner join/left join
	 * @param string[] $on_array  放置联接条件的数组，其数组数量与联接类型数量一致
	 * @param string[] $fields_array  放置所需要的字段，其与表的数组位置要对应，无须再写上表名称，只许写上字段名称即可，例如$table_array = array('a','b','c'); $fields_array = array('字段1','','字段1');那所需要的字段为a.字段1,c.字段1
	 * @param string $where_str  条件语句，形式如， and 表名.字段 = 值
	 * @param string[] $order_array  其用法与$fields_array类似
	 * @param string[] $group_array  其用法与$fields_array类似
	 * @param int $count 需要的记录数量
	 * @return var[]
	 */
	function GetJoinList(&$obj_page,$table_array,$join_str,$on_array,$fields_array = array('*'),$where_str = "",$count=0,$order_array = array(),$group_array = array()){
		//得到一个完整的SQL语句
		$list_sql = $this->GetJoinTableSql($table_array,$join_str,$on_array,$fields_array,$where_str,$order_array,$group_array);
		return $this->GetResultArray($list_sql,&$obj_page,$count);
	}

	/**
	 * 得到某一条记录
	 *
	 * @param string[] $table_array  放置表的数组
	 * @param string[] $join_str  放置表间连接类型，一般有inner join/left join
	 * @param string[] $on_array  放置联接条件的数组，其数组数量与联接类型数量一致
	 * @param string[] $fields_array  放置所需要的字段，其与表的数组位置要对应，无须再写上表名称，只许写上字段名称即可，例如$table_array = array('a','b','c'); $fields_array = array('字段1','','字段1');那所需要的字段为a.字段1,c.字段1
	 * @param string $where_str  条件语句，形式如， and 表名.字段 = 值
	 * @param string[] $order_array  其用法与$fields_array类似
	 * @param string[] $group_array  其用法与$fields_array类似
	 * @return string
	 */
	function GetOneJoinArray($table_array,$join_str,$on_array,$fields_array = array('*'),$where_str = "",$order_array = array(),$group_array = array()){
		//得到一个完整的SQL语句
		$list_sql = $this->GetJoinTableSql($table_array,$join_str,$on_array,$fields_array,$where_str,$order_array,$group_array);
		return $this->GetRow($list_sql);
	}

	/**
	 * 根据分页对象的设置，获取当前页面的数据列表
	 *
	 * @param string $sql_str 完整的SQL查询语句
	 * @param object $obj_page 分页对象
	 * @param int $count 需要的记录数量
	 * @return var[]
	 */
	function GetResultArray($sql_str,&$obj_page,$count = 0){
		//echo '$obj_page'.$obj_page;
		//echo '$count:'.$count;exit;
		if($obj_page != ""){
			$count_sql = $this->GetCountSql($sql_str);
			$arr = $this->GetRow($count_sql);
			$obj_page->setPage(array('total'=>$arr['count']));
			$list_rs = $this->SelectLimit($sql_str,$obj_page->pagebarnum,$obj_page->offset);
		}else if($count > 0){
			$list_rs = $this->SelectLimit($sql_str,$count,0);
		}else{
			if ($this->_dbconfig['engine_type'] == "adodb"){
				$list_rs = $this->Execute($sql_str);
			}else{
				$list_rs = $this->GetArray($sql_str);
			}
		}

		if ($list_rs != "" ){
			if ($this->_dbconfig['engine_type'] == "adodb"){
				$array = $list_rs->GetArray();
			}else{
				$array = $list_rs;
			}
			return $array;
		}else{
			echo  "{type:0,message:\"doesn't exist the database table or other database error! reference:$sql_str\"}";
			exit;
		}
	}

	/**
	 * 得到一个SQL语句相对应的计算记录数的SQL语句
	 *
	 * @param string $sql_str 完整的SQL查询语句
	 * @return string
	 */
	function GetCountSql($sql_str){
		$select_array = explode("from",$sql_str);
		$select_str = substr($select_array[0],6);
		$sql_str = str_replace($select_str," count(*) as count ",$sql_str);
		return $sql_str;
	}

	/**
	 * 组合成为一条SQL语句
	 *
	 * @param string $table_name  表名称
	 * @param string $condition  条件语句
	 * @param string $fields  所需要的字段
	 * @param string $order  排序语句
	 * @param string $group  分组语句
	 * @return string  所产生的SQL语句
	 */
	function GetSql($table_name,$condition = "",$fields = "*",$order = "",$group = ""){
		if (trim($order) != ""){
			$f_arr = explode(",",$order);
			foreach ($f_arr as $f_k=>$f_v){
				$f_arr[$f_k] = $table_name . "." . trim($f_v);
			}
			$order_str = " order by " . implode(",",$f_arr);
			unset($f_arr);
		}
		if (trim($group) != ""){
			$f_arr = explode(",",$group);
			foreach ($f_arr as $f_k=>$f_v){
				$f_arr[$f_k] = $table_name . "." . trim($f_v);
			}
			$group_str = " group by " . implode(",",$f_arr);
		}
		$rs_sql = "select {$fields} from `".NC_DBTABLEPRE."$table_name` as {$table_name} " . $this->_conditionWhere($condition . $order_str . $group_str);
		return $rs_sql;
	}

	/**
	 * 去掉别名前缀
	 *
	 * @param string $sql_str
	 * @param string $name
	 * @return string
	 */
	function _DropName($sql_str,$name = ""){
		return str_replace($name . ".", " " ,$sql_str);
	}
}
?>